﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;

namespace NLBS.Data
{
    public partial class SqlDataProvider:DbProvider
    {
        #region User


       public override bool ValidateUser(string username, string password)
       {
           
           DbCommand dbCommand = db.GetSqlStringCommand("SELECT user_name, user_password FROM dbo.blog_User where user_name=@username and user_password=@password");
           db.AddInParameter(dbCommand, "username", DbType.String, username);
           db.AddInParameter(dbCommand, "password", DbType.String, password);
           IDataReader dr = db.ExecuteReader(dbCommand);
           if (dr.Read())
           {
               DbCommand updateCommand = db.GetSqlStringCommand("update blog_user set user_lastvisit=@lastvisit");
               db.AddInParameter(updateCommand, "lastvisit", DbType.DateTime, DateTime.Now);
               db.ExecuteNonQuery(updateCommand);
               return true;
           }
           return false;
       }

        public override User GetUserByID(int userID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT  blog_User.*,blog_UserGroup.*,blog_UserGroup.group_Name AS groupName
            FROM      blog_User,blog_UserGroup
            WHERE   (user_id = @userID and blog_User.user_groupID=blog_UserGroup.group_id)");
            db.AddInParameter(dbCommand, "userID", DbType.Int32, userID);

            User user = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    user = CommonDataProvider.PopulateUserViewAndEditFromIDataReader(dataReader);
                    user.Group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                }
            }
            return user;
        }

        public override User GetUserByName(string userName)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT blog_User.*,blog_UserGroup.*,blog_UserGroup.group_Name AS groupName
            FROM      blog_User,blog_UserGroup
            WHERE   (user_name = @userName and blog_User.user_groupID=blog_UserGroup.group_id)");
            db.AddInParameter(dbCommand, "userName", DbType.String, userName);

            User user = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    user = CommonDataProvider.PopulateUserViewAndEditFromIDataReader(dataReader);
                    user.Group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                }
            }
            return user;
        }

        public override void UpdateUser(User user)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"UPDATE  blog_User
            SET         user_password = @user_password, user_salt = @user_salt, 
                            user_groupID = @user_groupID, user_gender = @user_gender, user_email = @user_email, 
                            user_hideEmail = @user_hideEmail, user_homepage = @user_homepage
            WHERE   (user_id = @user_id)");
            db.AddInParameter(dbCommand, "user_ID", DbType.Int32, user.UserID);
            db.AddInParameter(dbCommand, "user_password", DbType.String, user.Password);
            db.AddInParameter(dbCommand, "user_salt", DbType.String, user.Salt);
            db.AddInParameter(dbCommand, "user_groupID", DbType.Int32, user.GroupID);
            db.AddInParameter(dbCommand, "user_gender", DbType.Int32, user.Gender);
            db.AddInParameter(dbCommand, "user_email", DbType.String, user.Email);
            db.AddInParameter(dbCommand, "user_hideEmail", DbType.Boolean, user.HideEmail);
            db.AddInParameter(dbCommand, "user_homepage", DbType.String, user.Homepage);
            db.ExecuteNonQuery(dbCommand);
        }

        public override IList<User> GetUser()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT  blog_User.*,blog_UserGroup.*,blog_UserGroup.group_Name AS groupName
            FROM  blog_User,blog_UserGroup WHERE   (blog_User.user_groupID=blog_UserGroup.group_id)");

            IList<User> lists = new List<User>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    User user = CommonDataProvider.PopulateUserFromIDataReader(dataReader);
                    user.Group = CommonDataProvider.PopulateUserGroupFromIDataReader(dataReader);
                    lists.Add(user);
                }
            }
            return lists;
        }

        public override void InsertUser(User user)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"INSERT INTO [dbo].[blog_User] ([user_name], [user_password], 
            [user_salt], [user_groupID], [user_gender], [user_email], 
            [user_hideEmail], [user_homepage], [user_articleCount], 
            [user_commentCount], [user_lastVisit], [user_ip]) 
            VALUES 
            (@user_name, @user_password, @user_salt, @user_groupID, @user_gender, 
            @user_email, @user_hideEmail, @user_homepage, 
            @user_articleCount, @user_commentCount, @user_lastVisit, @user_ip);
            UPDATE blog_Settings SET set_value0 =set_value0+1  WHERE set_name='counterUser'");
            db.AddInParameter(dbCommand, "user_name", DbType.String, user.UserName);
            db.AddInParameter(dbCommand, "user_password", DbType.String, user.Password);
            db.AddInParameter(dbCommand, "user_salt", DbType.Int32, user.Salt);
            db.AddInParameter(dbCommand, "user_groupID", DbType.Int32, user.GroupID);
            db.AddInParameter(dbCommand, "user_gender", DbType.Int32, user.Gender);
            db.AddInParameter(dbCommand, "user_email", DbType.String, user.Email);
            db.AddInParameter(dbCommand, "user_hideEmail", DbType.Boolean, user.HideEmail);
            db.AddInParameter(dbCommand, "user_homepage", DbType.String, user.Homepage);
            db.AddInParameter(dbCommand, "user_articleCount", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "user_commentCount", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "user_lastVisit", DbType.DateTime, user.LastVisit);
            db.AddInParameter(dbCommand, "user_ip", DbType.String, user.IP);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void DeleteUser(int userID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand(@"DELETE FROM [dbo].[blog_User] WHERE (([user_id] = @user_id))
            UPDATE blog_Settings SET set_value0 =set_value0-1  WHERE set_name='counterUser'");
            db.AddInParameter(dbCommand, "user_ID", DbType.Int32, userID);
            db.ExecuteNonQuery(dbCommand);
        }

        #endregion
    }
}
